package dao;

import model.AttendanceRecord;
import model.User;
import model.Seat;
import util.DatabaseConnection;

import java.sql.*;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;

/**
 * 考勤数据访问对象
 */
public class AttendanceDAO {

    /**
     * 创建考勤记录
     */
    public boolean insertAttendanceRecord(AttendanceRecord record) throws SQLException {
        String sql = "INSERT INTO attendance_records (reservation_id, user_id, seat_id, reservation_date, " +
                "scheduled_start_time, scheduled_end_time, attendance_status) VALUES (?, ?, ?, ?, ?, ?, ?)";

        try (Connection conn = DatabaseConnection.getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {

            stmt.setInt(1, record.getReservationId());
            stmt.setInt(2, record.getUserId());
            stmt.setInt(3, record.getSeatId());
            stmt.setDate(4, Date.valueOf(record.getReservationDate()));
            stmt.setTime(5, Time.valueOf(record.getScheduledStartTime()));
            stmt.setTime(6, Time.valueOf(record.getScheduledEndTime()));
            stmt.setInt(7, record.getAttendanceStatus());

            int result = stmt.executeUpdate();
            if (result > 0) {
                try (ResultSet rs = stmt.getGeneratedKeys()) {
                    if (rs.next()) {
                        record.setAttendanceId(rs.getInt(1));
                    }
                }
                return true;
            }
            return false;
        }
    }

    /**
     * 执行签到操作
     */
    public CheckInResult checkIn(int reservationId, int userId) throws SQLException {
        // 首先检查是否存在CheckIn存储过程
        try (Connection conn = DatabaseConnection.getConnection()) {
            // 检查存储过程是否存在 - 使用更可靠的检查方法
            String checkSql = "SELECT COUNT(*) FROM information_schema.routines " +
                    "WHERE routine_schema = DATABASE() AND routine_name = 'CheckIn' AND routine_type = 'PROCEDURE'";
            try (PreparedStatement checkStmt = conn.prepareStatement(checkSql);
                    ResultSet rs = checkStmt.executeQuery()) {
                if (rs.next() && rs.getInt(1) == 0) {
                    return new CheckInResult(false, -1, "CheckIn存储过程不存在，请先执行complete_database_setup.sql");
                }
            }
        }

        String sql = "{CALL CheckIn(?, ?, ?, ?)}";

        try (Connection conn = DatabaseConnection.getConnection();
                CallableStatement stmt = conn.prepareCall(sql)) {

            // 设置输入参数
            stmt.setInt(1, reservationId);
            stmt.setInt(2, userId);

            // 注册输出参数
            stmt.registerOutParameter(3, Types.INTEGER);
            stmt.registerOutParameter(4, Types.VARCHAR);

            // 执行存储过程
            stmt.execute();

            // 获取输出参数
            int result = stmt.getInt(3);
            String message = stmt.getString(4);

            return new CheckInResult(result == 0, result, message);
        } catch (SQLException e) {
            System.err.println("签到存储过程调用失败: " + e.getMessage());
            return new CheckInResult(false, -1, "签到失败: " + e.getMessage());
        }
    }

    /**
     * 执行签退操作
     */
    public CheckOutResult checkOut(int reservationId, int userId) throws SQLException {
        // 首先检查是否存在CheckOut存储过程
        try (Connection conn = DatabaseConnection.getConnection()) {
            // 检查存储过程是否存在 - 使用更可靠的检查方法
            String checkSql = "SELECT COUNT(*) FROM information_schema.routines " +
                    "WHERE routine_schema = DATABASE() AND routine_name = 'CheckOut' AND routine_type = 'PROCEDURE'";
            try (PreparedStatement checkStmt = conn.prepareStatement(checkSql);
                    ResultSet rs = checkStmt.executeQuery()) {
                if (rs.next() && rs.getInt(1) == 0) {
                    return new CheckOutResult(false, -1, "CheckOut存储过程不存在，请先执行complete_database_setup.sql");
                }
            }
        }

        String sql = "{CALL CheckOut(?, ?, ?, ?)}";

        try (Connection conn = DatabaseConnection.getConnection();
                CallableStatement stmt = conn.prepareCall(sql)) {

            stmt.setInt(1, reservationId);
            stmt.setInt(2, userId);
            stmt.registerOutParameter(3, Types.INTEGER);
            stmt.registerOutParameter(4, Types.VARCHAR);

            stmt.execute();

            int result = stmt.getInt(3);
            String message = stmt.getString(4);

            return new CheckOutResult(result == 0, result, message);
        } catch (SQLException e) {
            System.err.println("签退存储过程调用失败: " + e.getMessage());
            return new CheckOutResult(false, -1, "签退失败: " + e.getMessage());
        }
    }

    /**
     * 根据预约ID获取考勤记录
     */
    public AttendanceRecord getAttendanceByReservationId(int reservationId) throws SQLException {
        String sql = "SELECT * FROM attendance_records WHERE reservation_id = ?";

        try (Connection conn = DatabaseConnection.getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, reservationId);

            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    return mapResultSetToAttendanceRecord(rs);
                }
            }
        }
        return null;
    }

    /**
     * 获取用户的考勤记录
     */
    public List<AttendanceRecord> getUserAttendanceRecords(int userId, LocalDate startDate, LocalDate endDate)
            throws SQLException {
        String sql = "SELECT ar.*, u.student_id, u.name as user_name, s.seat_number, s.area, " +
                "r.start_time, r.end_time " +
                "FROM attendance_records ar " +
                "LEFT JOIN users u ON ar.user_id = u.user_id " +
                "LEFT JOIN seats s ON ar.seat_id = s.seat_id " +
                "LEFT JOIN reservations r ON ar.reservation_id = r.reservation_id " +
                "WHERE ar.user_id = ? ";

        if (startDate != null && endDate != null) {
            sql += "AND ar.reservation_date BETWEEN ? AND ? ";
        }
        sql += "ORDER BY ar.reservation_date DESC, ar.scheduled_start_time DESC";

        List<AttendanceRecord> records = new ArrayList<>();

        try (Connection conn = DatabaseConnection.getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, userId);
            if (startDate != null && endDate != null) {
                stmt.setDate(2, Date.valueOf(startDate));
                stmt.setDate(3, Date.valueOf(endDate));
            }

            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    AttendanceRecord record = mapResultSetToAttendanceRecord(rs);

                    // 设置关联对象信息
                    User user = new User();
                    user.setUserId(rs.getInt("user_id"));
                    user.setStudentId(rs.getString("student_id"));
                    user.setName(rs.getString("user_name"));
                    record.setUser(user);

                    Seat seat = new Seat();
                    seat.setSeatId(rs.getInt("seat_id"));
                    seat.setSeatNumber(rs.getString("seat_number"));
                    seat.setArea(rs.getString("area"));
                    record.setSeat(seat);

                    records.add(record);
                }
            }
        }
        return records;
    }

    /**
     * 获取所有考勤记录（管理员使用）
     */
    public List<AttendanceRecord> getAllAttendanceRecords(LocalDate startDate, LocalDate endDate,
            Integer attendanceStatus) throws SQLException {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT ar.*, u.student_id, u.name as user_name, s.seat_number, s.area ");
        sql.append("FROM attendance_records ar ");
        sql.append("LEFT JOIN users u ON ar.user_id = u.user_id ");
        sql.append("LEFT JOIN seats s ON ar.seat_id = s.seat_id ");
        sql.append("WHERE 1=1 ");

        List<Object> params = new ArrayList<>();

        if (startDate != null && endDate != null) {
            sql.append("AND ar.reservation_date BETWEEN ? AND ? ");
            params.add(Date.valueOf(startDate));
            params.add(Date.valueOf(endDate));
        }

        if (attendanceStatus != null) {
            sql.append("AND ar.attendance_status = ? ");
            params.add(attendanceStatus);
        }

        sql.append("ORDER BY ar.reservation_date DESC, ar.scheduled_start_time DESC");

        List<AttendanceRecord> records = new ArrayList<>();

        try (Connection conn = DatabaseConnection.getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql.toString())) {

            for (int i = 0; i < params.size(); i++) {
                stmt.setObject(i + 1, params.get(i));
            }

            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    AttendanceRecord record = mapResultSetToAttendanceRecord(rs);

                    // 设置关联对象信息
                    User user = new User();
                    user.setUserId(rs.getInt("user_id"));
                    user.setStudentId(rs.getString("student_id"));
                    user.setName(rs.getString("user_name"));
                    record.setUser(user);

                    Seat seat = new Seat();
                    seat.setSeatId(rs.getInt("seat_id"));
                    seat.setSeatNumber(rs.getString("seat_number"));
                    seat.setArea(rs.getString("area"));
                    record.setSeat(seat);

                    records.add(record);
                }
            }
        }
        return records;
    }

    /**
     * 处理逾期预约
     */
    public void processOverdueReservations() throws SQLException {
        String sql = "{CALL ProcessOverdueReservations()}";

        try (Connection conn = DatabaseConnection.getConnection();
                CallableStatement stmt = conn.prepareCall(sql)) {

            stmt.execute();
        }
    }

    /**
     * 更新用户连续使用记录
     */
    public void updateUserStreaks() throws SQLException {
        String sql = "{CALL UpdateUserStreaks()}";

        try (Connection conn = DatabaseConnection.getConnection();
                CallableStatement stmt = conn.prepareCall(sql)) {

            stmt.execute();
        }
    }

    /**
     * 获取用户今天是否有可以签到/签退的预约
     */
    public List<AttendanceRecord> getUserTodayActiveAttendance(int userId) throws SQLException {
        String sql = "SELECT ar.*, s.seat_number, s.area " +
                "FROM attendance_records ar " +
                "LEFT JOIN seats s ON ar.seat_id = s.seat_id " +
                "WHERE ar.user_id = ? AND ar.reservation_date = CURDATE() " +
                "AND ar.attendance_status = 0 " +
                "ORDER BY ar.scheduled_start_time";

        List<AttendanceRecord> records = new ArrayList<>();

        try (Connection conn = DatabaseConnection.getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, userId);

            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    AttendanceRecord record = mapResultSetToAttendanceRecord(rs);

                    Seat seat = new Seat();
                    seat.setSeatId(rs.getInt("seat_id"));
                    seat.setSeatNumber(rs.getString("seat_number"));
                    seat.setArea(rs.getString("area"));
                    record.setSeat(seat);

                    records.add(record);
                }
            }
        }
        return records;
    }

    /**
     * 将ResultSet映射到AttendanceRecord对象
     */
    private AttendanceRecord mapResultSetToAttendanceRecord(ResultSet rs) throws SQLException {
        AttendanceRecord record = new AttendanceRecord();

        record.setAttendanceId(rs.getInt("attendance_id"));
        record.setReservationId(rs.getInt("reservation_id"));
        record.setUserId(rs.getInt("user_id"));
        record.setSeatId(rs.getInt("seat_id"));
        record.setReservationDate(rs.getDate("reservation_date").toLocalDate());
        record.setScheduledStartTime(rs.getTime("scheduled_start_time").toLocalTime());
        record.setScheduledEndTime(rs.getTime("scheduled_end_time").toLocalTime());

        Timestamp checkInTime = rs.getTimestamp("check_in_time");
        if (checkInTime != null) {
            record.setCheckInTime(checkInTime.toLocalDateTime());
        }
        record.setCheckedIn(rs.getBoolean("is_checked_in"));
        record.setCheckInStatus(rs.getInt("check_in_status"));

        Timestamp checkOutTime = rs.getTimestamp("check_out_time");
        if (checkOutTime != null) {
            record.setCheckOutTime(checkOutTime.toLocalDateTime());
        }
        record.setCheckedOut(rs.getBoolean("is_checked_out"));
        record.setCheckOutStatus(rs.getInt("check_out_status"));

        record.setActualDuration(rs.getInt("actual_duration"));
        record.setAttendanceStatus(rs.getInt("attendance_status"));

        record.setCreateTime(rs.getTimestamp("create_time").toLocalDateTime());
        record.setUpdateTime(rs.getTimestamp("update_time").toLocalDateTime());

        return record;
    }

    /**
     * 签到结果类
     */
    public static class CheckInResult {
        private boolean success;
        private int resultCode;
        private String message;

        public CheckInResult(boolean success, int resultCode, String message) {
            this.success = success;
            this.resultCode = resultCode;
            this.message = message;
        }

        public boolean isSuccess() {
            return success;
        }

        public int getResultCode() {
            return resultCode;
        }

        public String getMessage() {
            return message;
        }
    }

    /**
     * 签退结果类
     */
    public static class CheckOutResult {
        private boolean success;
        private int resultCode;
        private String message;

        public CheckOutResult(boolean success, int resultCode, String message) {
            this.success = success;
            this.resultCode = resultCode;
            this.message = message;
        }

        public boolean isSuccess() {
            return success;
        }

        public int getResultCode() {
            return resultCode;
        }

        public String getMessage() {
            return message;
        }
    }

    /**
     * 获取用户今天的所有预约（包括已预约但未创建考勤记录的）
     */
    public List<AttendanceRecord> getUserTodayAllReservations(int userId) throws SQLException {
        String sql = "SELECT " +
                "    COALESCE(ar.attendance_id, 0) as attendance_id, " +
                "    r.reservation_id, " +
                "    r.user_id, " +
                "    r.seat_id, " +
                "    r.reservation_date, " +
                "    r.start_time as scheduled_start_time, " +
                "    r.end_time as scheduled_end_time, " +
                "    COALESCE(ar.check_in_time, NULL) as check_in_time, " +
                "    COALESCE(ar.check_out_time, NULL) as check_out_time, " +
                "    COALESCE(ar.is_checked_in, FALSE) as is_checked_in, " +
                "    COALESCE(ar.is_checked_out, FALSE) as is_checked_out, " +
                "    COALESCE(ar.check_in_status, 0) as check_in_status, " +
                "    COALESCE(ar.check_out_status, 0) as check_out_status, " +
                "    COALESCE(ar.actual_duration, 0) as actual_duration, " +
                "    COALESCE(ar.attendance_status, -1) as attendance_status, " +
                "    s.seat_number, s.area " +
                "FROM reservations r " +
                "LEFT JOIN attendance_records ar ON r.reservation_id = ar.reservation_id " +
                "LEFT JOIN seats s ON r.seat_id = s.seat_id " +
                "WHERE r.user_id = ? AND r.reservation_date = CURDATE() AND r.status IN (0, 2) " +
                "ORDER BY r.start_time";

        List<AttendanceRecord> records = new ArrayList<>();

        try (Connection conn = DatabaseConnection.getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, userId);

            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    AttendanceRecord record = new AttendanceRecord();

                    // 基本信息
                    record.setAttendanceId(rs.getInt("attendance_id"));
                    record.setReservationId(rs.getInt("reservation_id"));
                    record.setUserId(rs.getInt("user_id"));
                    record.setSeatId(rs.getInt("seat_id"));
                    record.setReservationDate(rs.getDate("reservation_date").toLocalDate());
                    record.setScheduledStartTime(rs.getTime("scheduled_start_time").toLocalTime());
                    record.setScheduledEndTime(rs.getTime("scheduled_end_time").toLocalTime());

                    // 签到签退信息
                    Timestamp checkInTime = rs.getTimestamp("check_in_time");
                    if (checkInTime != null) {
                        record.setCheckInTime(checkInTime.toLocalDateTime());
                    }

                    Timestamp checkOutTime = rs.getTimestamp("check_out_time");
                    if (checkOutTime != null) {
                        record.setCheckOutTime(checkOutTime.toLocalDateTime());
                    }

                    record.setCheckedIn(rs.getBoolean("is_checked_in"));
                    record.setCheckedOut(rs.getBoolean("is_checked_out"));
                    record.setCheckInStatus(rs.getInt("check_in_status"));
                    record.setCheckOutStatus(rs.getInt("check_out_status"));
                    record.setActualDuration(rs.getInt("actual_duration"));
                    record.setAttendanceStatus(rs.getInt("attendance_status"));

                    // 座位信息
                    Seat seat = new Seat();
                    seat.setSeatId(rs.getInt("seat_id"));
                    seat.setSeatNumber(rs.getString("seat_number"));
                    seat.setArea(rs.getString("area"));
                    record.setSeat(seat);

                    records.add(record);
                }
            }
        }

        return records;
    }

    /**
     * 获取用户指定日期范围内的所有预约（包括已预约但未创建考勤记录的）
     */
    public List<AttendanceRecord> getUserAllReservations(int userId, LocalDate startDate, LocalDate endDate)
            throws SQLException {
        String sql = "SELECT " +
                "    COALESCE(ar.attendance_id, 0) as attendance_id, " +
                "    r.reservation_id, " +
                "    r.user_id, " +
                "    r.seat_id, " +
                "    r.reservation_date, " +
                "    r.start_time as scheduled_start_time, " +
                "    r.end_time as scheduled_end_time, " +
                "    COALESCE(ar.check_in_time, NULL) as check_in_time, " +
                "    COALESCE(ar.check_out_time, NULL) as check_out_time, " +
                "    COALESCE(ar.is_checked_in, FALSE) as is_checked_in, " +
                "    COALESCE(ar.is_checked_out, FALSE) as is_checked_out, " +
                "    COALESCE(ar.check_in_status, 0) as check_in_status, " +
                "    COALESCE(ar.check_out_status, 0) as check_out_status, " +
                "    COALESCE(ar.actual_duration, 0) as actual_duration, " +
                "    COALESCE(ar.attendance_status, -1) as attendance_status, " +
                "    s.seat_number, s.area, " +
                "    COALESCE(ar.create_time, r.create_time) as create_time, " +
                "    COALESCE(ar.update_time, r.create_time) as update_time " +
                "FROM reservations r " +
                "LEFT JOIN attendance_records ar ON r.reservation_id = ar.reservation_id " +
                "LEFT JOIN seats s ON r.seat_id = s.seat_id " +
                "WHERE r.user_id = ? AND r.status IN (0, 2) ";

        if (startDate != null && endDate != null) {
            sql += "AND r.reservation_date BETWEEN ? AND ? ";
        }

        sql += "ORDER BY r.reservation_date DESC, r.start_time DESC";

        List<AttendanceRecord> records = new ArrayList<>();

        try (Connection conn = DatabaseConnection.getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, userId);
            if (startDate != null && endDate != null) {
                stmt.setDate(2, Date.valueOf(startDate));
                stmt.setDate(3, Date.valueOf(endDate));
            }

            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    AttendanceRecord record = new AttendanceRecord();

                    // 基本信息
                    record.setAttendanceId(rs.getInt("attendance_id"));
                    record.setReservationId(rs.getInt("reservation_id"));
                    record.setUserId(rs.getInt("user_id"));
                    record.setSeatId(rs.getInt("seat_id"));
                    record.setReservationDate(rs.getDate("reservation_date").toLocalDate());
                    record.setScheduledStartTime(rs.getTime("scheduled_start_time").toLocalTime());
                    record.setScheduledEndTime(rs.getTime("scheduled_end_time").toLocalTime());

                    // 签到签退信息
                    Timestamp checkInTime = rs.getTimestamp("check_in_time");
                    if (checkInTime != null) {
                        record.setCheckInTime(checkInTime.toLocalDateTime());
                    }

                    Timestamp checkOutTime = rs.getTimestamp("check_out_time");
                    if (checkOutTime != null) {
                        record.setCheckOutTime(checkOutTime.toLocalDateTime());
                    }

                    record.setCheckedIn(rs.getBoolean("is_checked_in"));
                    record.setCheckedOut(rs.getBoolean("is_checked_out"));
                    record.setCheckInStatus(rs.getInt("check_in_status"));
                    record.setCheckOutStatus(rs.getInt("check_out_status"));
                    record.setActualDuration(rs.getInt("actual_duration"));
                    record.setAttendanceStatus(rs.getInt("attendance_status"));

                    // 时间戳
                    record.setCreateTime(rs.getTimestamp("create_time").toLocalDateTime());
                    record.setUpdateTime(rs.getTimestamp("update_time").toLocalDateTime());

                    // 座位信息
                    Seat seat = new Seat();
                    seat.setSeatId(rs.getInt("seat_id"));
                    seat.setSeatNumber(rs.getString("seat_number"));
                    seat.setArea(rs.getString("area"));
                    record.setSeat(seat);

                    records.add(record);
                }
            }
        }

        return records;
    }
}
